<?php
  require_once("header.inc.php");
  
  // Pegar número do caso pai
  $casoPaiNumero = NULL;
  
  if (isset($_GET["auto"])) {
      $casoPaiNumero = $_POST["casopainumero"];
  } else {
    if (isset($_GET['cp'])) {
      $casoPaiNumero = $_GET['cp'];  
    } else {
      exit; 
    }
  }
  
  $query = <<<EOS
-- CASOS FILHOS
  SELECT BUT.id AS caso
       , BUT.status
       , PRT.name AS sistema
       , False AS backlog
       , todo(BUT.id,BUT.handler_id,'%s',BUT.status,%u,%u,%u,%u) AS todo
       , inprogress(BUT.id,BUT.handler_id,'%s',BUT.status,%u,%u,%u,%u) AS inprogress
--       , toverify(BUT.id,BUT.handler_id,'%%s',BUT.status,%%u,%%u,%%u,%%u) AS toverify
--       , verified(BUT.id,BUT.handler_id,'%%s',BUT.status,%%u,%%u,%%u,%%u) AS verified
       , ugc1(BUT.id,BUT.handler_id,'%s',BUT.status,%u,%u,%u,%u) AS ugc1
       , uts(BUT.id,BUT.handler_id,'%s',BUT.status,%u,%u,%u,%u,%u) AS uts
       , ugc2(BUT.id,BUT.handler_id,'%s',BUT.status,%u,%u,%u,%u) AS ugc2
       , un(BUT.id,BUT.handler_id,'%s',BUT.status,%u,%u,%u,%u,%u,%u) AS un
       , ugc3(BUT.id,BUT.handler_id,'%s',BUT.status,%u,%u,%u,%u) AS ugc3
       , BUT.status = %u AS rejeitado
       , BUT.status = %u AS impedimento
       , BUT.status = %u AS emproducao
       , BUT.status = %u AS fechado
       , COALESCE((SELECT COALESCE(CFS.value,CFT.default_value) FROM mantis_custom_field_string_table CFS JOIN mantis_custom_field_table CFT ON CFT.id = CFS.field_id WHERE CFS.field_id = %u AND CFS.bug_id = BUT.id),'0') AS storypoints
       , REPLACE(COALESCE((SELECT COALESCE(CFS.value,CFT.default_value) FROM mantis_custom_field_string_table CFS JOIN mantis_custom_field_table CFT ON CFT.id = CFS.field_id WHERE CFS.field_id = %u AND CFS.bug_id = BUT.id),'0'),'%%','') AS completude
       , COALESCE(BUT.target_version,'0.0.0.0') AS versao
       , comtag(BUT.id,%u) as offsprint
       , False As pendente
       , BUT.priority AS prioridade
       , BUT.date_submitted AS dataenvio
    FROM mantis_bug_table BUT
    JOIN mantis_project_table PRT ON PRT.id = BUT.project_id
   WHERE filhode(BUT.id,%u)
     AND BUT.project_id <> %u
UNION
-- BACKLOG
  SELECT BUT.id AS caso
       , BUT.status
       , PRT.name AS sistema
       , True AS backlog
       , False AS todo
       , False AS inprogress
--       , False AS toverify
--       , False AS verified
       , False AS ugc1
       , False AS uts
       , False AS ugc2
       , False AS un
       , False AS ugc3
       , False AS rejeitado
       , False AS impedimento
       , False AS emproducao
       , False AS fechado
       , COALESCE((SELECT COALESCE(CFS.value,CFT.default_value) FROM mantis_custom_field_string_table CFS JOIN mantis_custom_field_table CFT ON CFT.id = CFS.field_id WHERE CFS.field_id = %u AND CFS.bug_id = BUT.id),'0') AS storypoints
       , REPLACE(COALESCE((SELECT COALESCE(CFS.value,CFT.default_value) FROM mantis_custom_field_string_table CFS JOIN mantis_custom_field_table CFT ON CFT.id = CFS.field_id WHERE CFS.field_id = %u AND CFS.bug_id = BUT.id),'0'),'%%','') AS completude
       , COALESCE(BUT.target_version,'0.0.0.0') AS versao
       , False AS offsprint
       , False As pendente
       , BUT.priority AS prioridade
       , BUT.date_submitted AS dataenvio
    FROM mantis_bug_table BUT
    JOIN mantis_project_table PRT ON PRT.id = BUT.project_id
   WHERE BUT.handler_id = %u
     AND BUT.project_id <> %u
     AND NOT filhodepaidesprint(BUT.id,%u,%u)
     AND BUT.status = %u
UNION
-- PENDENTES
  SELECT BUT.id AS caso
       , BUT.status
       , PRT.name AS sistema
       , False AS backlog
       , False AS todo
       , False AS inprogress
--       , False AS toverify
--       , False AS verified
       , False AS ugc1
       , False AS uts
       , False AS ugc2
       , False AS un
       , False AS ugc3
       , False AS rejeitado
       , False AS impedimento
       , False AS emproducao
       , False AS fechado
       , COALESCE((SELECT COALESCE(CFS.value,CFT.default_value) FROM mantis_custom_field_string_table CFS JOIN mantis_custom_field_table CFT ON CFT.id = CFS.field_id WHERE CFS.field_id = %u AND CFS.bug_id = BUT.id),'0') AS storypoints
       , REPLACE(COALESCE((SELECT COALESCE(CFS.value,CFT.default_value) FROM mantis_custom_field_string_table CFS JOIN mantis_custom_field_table CFT ON CFT.id = CFS.field_id WHERE CFS.field_id = %u AND CFS.bug_id = BUT.id),'0'),'%%','') AS completude
       , COALESCE(BUT.target_version,'0.0.0.0') AS versao
       , False AS offsprint
       , True As pendente
       , BUT.priority AS prioridade
       , BUT.date_submitted AS dataenvio
    FROM mantis_bug_table BUT
    JOIN mantis_project_table PRT ON PRT.id = BUT.project_id
   WHERE BUT.status = %u
     AND BUT.handler_id = %u
     AND filhodepaidesprint(BUT.id,%u,%u)
     AND NOT filhode(BUT.id,%u)
ORDER BY prioridade DESC, dataenvio
EOS;
  
  // as funções usadas na clausula select não são garantidas quanto ao seu funcionamento isolado
  // elas funcionam corretamente apenas quando aplicadas no cojunto de dados retornado pelo select
  // acima
  
  $snapshot = executarSQL(sprintf($query
                                 // CASOS FILHOS
                                 ,tokenizar($SESS_UNIDADE->getIdUsuariosLotados()),ID_STATUS_A_EXECUTAR,$casoPaiNumero,ID_TAG_TO_VERIFY,ID_TAG_VERIFIED                 // TODO
                                 ,tokenizar($SESS_UNIDADE->getIdUsuariosLotados()),ID_STATUS_EM_EXECUCAO,$casoPaiNumero,ID_TAG_TO_VERIFY,ID_TAG_VERIFIED                // IN PROGRESS
//                                 ,tokenizar($SESS_UNIDADE->getIdUsuariosLotados()),ID_STATUS_EM_EXECUCAO,$casoPaiNumero,ID_TAG_TO_VERIFY,ID_TAG_VERIFIED                // TO VERIFY
//                                 ,tokenizar($SESS_UNIDADE->getIdUsuariosLotados()),ID_STATUS_EM_EXECUCAO,$casoPaiNumero,ID_TAG_TO_VERIFY,ID_TAG_VERIFIED                // VERIFIED
                                 ,tokenizar(ID_USUARIOS_UGC),ID_STATUS_EM_EXECUCAO,$casoPaiNumero,ID_TAG_TO_VERIFY,ID_TAG_VERIFIED                                      // UGC1
                                 ,tokenizar(ID_USUARIOS_UTS),ID_STATUS_EM_TESTES,ID_STATUS_EM_EXECUCAO,$casoPaiNumero,ID_TAG_TO_VERIFY,ID_TAG_VERIFIED                  // UTS
                                 ,tokenizar(ID_USUARIOS_UGC),ID_STATUS_EM_TESTES,$casoPaiNumero,ID_TAG_TO_VERIFY,ID_TAG_VERIFIED                                        // UGC2
                                 ,tokenizar(ID_USUARIOS_UN),ID_STATUS_LIB_HOMOL,ID_STATUS_EM_HOMOL,ID_STATUS_HOMOLOGADO,$casoPaiNumero,ID_TAG_TO_VERIFY,ID_TAG_VERIFIED // UN
                                 ,tokenizar(ID_USUARIOS_UGC),ID_STATUS_HOMOLOGADO,$casoPaiNumero,ID_TAG_TO_VERIFY,ID_TAG_VERIFIED                                       // UGC3
                                 ,ID_STATUS_REJEITADO                                                                                                                   // REJEITADO
                                 ,ID_STATUS_IMPEDIMENTO                                                                                                                 // IMPEDIMENTO
                                 ,ID_STATUS_EM_PRODUCAO                                                                                                                 // EM PRODUÇÃO
                                 ,ID_STATUS_FECHADO                                                                                                                     // FECHADO
                                 ,ID_CAMPO_STORYPOINT                                                                                                                   // STORY POINTS
                                 ,ID_CAMPO_COMPLETUDE                                                                                                                   // % COMPLETUDE
                                 ,ID_TAG_OFFSPRINT                                                                                                                      // OFFSPRINT
                                 // WHERE
                                 ,$casoPaiNumero
                                 ,ID_PROJETO_PAI
                                 // BACKLOG
                                 ,ID_CAMPO_STORYPOINT                                                                                                                   // STORY POINTS
                                 ,ID_CAMPO_COMPLETUDE                                                                                                                   // % COMPLETUDE
                                 // WHERE
                                 ,$SESS_UNIDADE->getIdUsuario()
                                 ,ID_PROJETO_PAI
                                 ,$SESS_UNIDADE->getIdUsuario()
                                 ,ID_PROJETO_PAI
                                 ,ID_STATUS_A_EXECUTAR
                                 // PENDENTES
                                 ,ID_CAMPO_STORYPOINT
                                 ,ID_CAMPO_COMPLETUDE
                                 // WHERE
                                 ,ID_STATUS_REJEITADO
                                 ,$SESS_UNIDADE->getIdUsuario()
                                 ,$SESS_UNIDADE->getIdUsuario()
                                 ,ID_PROJETO_PAI
                                 ,$casoPaiNumero));
  
  // Estatísticas do caso pai                               
  $query = <<<EOS
SELECT SUM(T.filhos) AS filhos
     , SUM(T.storypoints) AS storypoints
     , SUM(T.mediastorypoints) AS mediastorypoints
     , SUM(T.offsprints) AS offsprints
  FROM (SELECT COUNT(BRT.id) AS filhos
             , SUM(CAST(CASE CFS.value WHEN '?' THEN '0' WHEN '½' THEN '0.5' ELSE CFS.value END AS DECIMAL)) AS storypoints
             , AVG(CAST(CASE CFS.value WHEN '?' THEN '0' WHEN '½' THEN '0.5' ELSE CFS.value END AS DECIMAL)) AS mediastorypoints
             , 0 AS offsprints
          FROM mantis_bug_relationship_table BRT
     LEFT JOIN mantis_custom_field_string_table CFS ON CFS.bug_id = BRT.destination_bug_id and coalesce(CFS.field_id,%u) = %u
         WHERE BRT.relationship_type = 2
           AND BRT.source_bug_id = %u
           AND coalesce(CFS.value,'0') IN ('0','½','1','2','3','5','8','13','20','40','100','?')
     UNION
        SELECT 0 AS filhos
             , 0 AS storypoints
             , 0 AS mediastorypoints
             , COUNT(BRT.id) AS offsprints
          FROM mantis_bug_relationship_table BRT
         WHERE BRT.relationship_type = 2
           AND BRT.source_bug_id = %u
           AND comtag(BRT.destination_bug_id,%u)
       ) T   
EOS;
                                                                
  $estatCasoPaiSprint = obterRegistro(executarSQL(sprintf($query,ID_CAMPO_STORYPOINT,ID_CAMPO_STORYPOINT,$casoPaiNumero,$casoPaiNumero,ID_TAG_OFFSPRINT)));
  
  // Estatísticas gerais
  $query = <<<EOS
  SELECT COUNT(BUT.id) AS backlog
    FROM mantis_bug_table BUT
    JOIN mantis_project_table PRT ON PRT.id = BUT.project_id
   WHERE BUT.handler_id = %u
     AND BUT.project_id <> %u
     AND NOT filhodepaidesprint(BUT.id,%u,%u)
     AND BUT.status = %u
EOS;

  $estatGeral = obterRegistro(executarSQL(sprintf($query,$SESS_UNIDADE->getIdUsuario(),ID_PROJETO_PAI,$SESS_UNIDADE->getIdUsuario(),ID_PROJETO_PAI,ID_STATUS_A_EXECUTAR)));
  
  $caso = new stdClass();
  $caso->cf = $estatCasoPaiSprint->filhos;
  $caso->pt = number_format($estatCasoPaiSprint->storypoints,2,",",".");
  $caso->pm = number_format($estatCasoPaiSprint->mediastorypoints,2,",",".");
  $caso->os = $estatCasoPaiSprint->offsprints;
  $caso->pb = $estatGeral->backlog;
  
  while ($linha = obterRegistro($snapshot)) {
    unset($situacao);
                                      
    $situacao["caso"] = $linha->caso;
    $situacao["sistema"] = acertarNomeSistema($linha->sistema,$SESS_UNIDADE->getPrefixosRemoviveis(),$SESS_UNIDADE->getAbreviaturas());
    $situacao["storypoints"] = $linha->storypoints;       
    $situacao["completude"] = IntVal($linha->completude);
    $situacao["versao"] = $linha->versao;
    $situacao["offsprint"] = (boolean) $linha->offsprint;
    $situacao["prioridade"] = IntVal($linha->prioridade);

    if (toBoolean($linha->backlog)) {
      $situacao["status"] = "backlog";
    } else if (toBoolean($linha->pendente)) {
      $situacao["status"] = "pendente";
    } else if (toBoolean($linha->todo)) {
      $situacao["status"] = "todo";
    } else if (toBoolean($linha->inprogress)) {
      $situacao["status"] = "inprogress";  
    } else if (toBoolean($linha->ugc1)) {
      $situacao["status"] = "ugc1";  
    } else if (toBoolean($linha->uts)) {
      $situacao["status"] = "uts";  
    } else if (toBoolean($linha->ugc2)) {
      $situacao["status"] = "ugc2";  
    } else if (toBoolean($linha->un)) {
      $situacao["status"] = "un";  
    } else if (toBoolean($linha->ugc3)) {
      $situacao["status"] = "ugc3";  
    } else if (toBoolean($linha->emproducao)) {
      $situacao["status"] = "emproducao";  
    } else if (toBoolean($linha->rejeitado)) {
      $situacao["status"] = "rejeitado";  
    } else if (toBoolean($linha->impedimento)) {
      $situacao["status"] = "impedimento";  
    } else if (toBoolean($linha->fechado)) {
      $situacao["status"] = "fechado";  
    } else {
      $situacao["status"] = "invalido";  
    }
   
    $caso->postIts[] = $situacao;
  }

  header("Content-type: text/javascript; charset=utf-8");
  echo(json_encode($caso));
?>